package com.roc.service.impl;

import java.io.File;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jboss.logging.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.roc.dao.IBaseDao;
import com.roc.dao.ISQLBaseDao;
import com.roc.newPage.IPageDao;
import com.roc.pojo.Operators;
import com.roc.service.ServiceException;
import com.roc.service.agent.IAgncyService;
import com.roc.util.Constance;
import com.roc.util.constant.Role;
import com.roc.web.action.agent.AgencyActionSearchCondition;
@Service
@Transactional
public class AgncyServiceImpl  implements IAgncyService{
	static Logger logger=Logger.getLogger(AgncyServiceImpl.class);
    
	@Autowired
	private ISQLBaseDao sqlBaseDao;
	@Autowired
	 private IPageDao pageDao;
	
	@Autowired
	 private IBaseDao baseDao;
	
	
	@Override
	public Map<String, Object> importMidList(File file, Operators op, AgencyActionSearchCondition searchCondition ) throws ServiceException {
		// TODO Auto-generated method stub
		Map<Integer,List<Object>> uploadData=new LinkedHashMap<Integer, List<Object>>();//存放上传数据
		Map<String,Object> resultMap=new LinkedHashMap<String, Object>();//上传文件错误原因及定位
		
		try{		
		//上传Excel文件流转换为Map集合
		logger.info("上传Excel文件流转换为Map集合");
		boolean uploadResult=transfromToExcel(file, (LinkedHashMap<Integer, List<Object>>) uploadData,(LinkedHashMap<String, Object>) resultMap);
		if(!uploadResult)
		{
			return resultMap;//返回错误原因
		}
		//验证Excel表数据正确性
		logger.info("验证Excel表数据正确性");
		boolean correntValidate=verifyDataCorrectness(uploadData, resultMap);
		if(!correntValidate)
		{
			return resultMap;//返回校验原因
		}
		 logger.info("开始进行数据保存操作");	
		 boolean insertData = insertData(uploadData, resultMap,searchCondition,op);
		 if(!insertData)
			{
				return resultMap;//返回校验原因
			}
		   return resultMap;
		}catch(Exception e){
			logger.debug(e.getMessage(), e);
			throw new ServiceException("批量导入商户号失败");
		}
		
	}
	
	private boolean insertData(Map<Integer, List<Object>> uploadData,Map<String, Object> resultMap, AgencyActionSearchCondition searchCondition, Operators op) {
		Iterator<Entry<Integer, List<Object>>> it=uploadData.entrySet().iterator();	
		//封装数据
		
		int count=0;
		it=uploadData.entrySet().iterator(); 
		List<List<Object>> res = new ArrayList<List<Object>>();
		while(it.hasNext())
		{
			List<Object> resultData = new ArrayList<Object>();
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();
			StringBuffer sb = new StringBuffer();
			try {
				sb=sb.append("SELECT t.mcht_cd,t.name_busi,t.name , "
							+ " (SELECT a.name FROM  tbl_area  a WHERE a.id=(( SELECT op.companyid FROM t_operators op WHERE  op.id=TO_number(t.ext1) )))  comName , "
							+ "  (SELECT O.ORG_NAME FROM  TBL_ORG_MAIN   O WHERE O.ORG_CODE=(( SELECT op.oneagentid FROM t_operators op WHERE  op.id=TO_number(t.ext1) )))   OrgName"
							+ "  FROM tbl_mcht_inf  t WHERE t.mcht_cd=? ");
				if(!"".equals(searchCondition.getOneOrgCode())&&searchCondition.getOneOrgCode()!=null&&!searchCondition.getRoleId().equals(Role.SYSTEM.getCode())&&!(searchCondition.getRoleId().equals(Role.MARKET_DIRECT.getCode())||searchCondition.getRoleId().equals(Role.MARKET_LEAD.getCode()))){
					sb.append(" and  t.uc_bc_cd_area ="+searchCondition.getOneOrgCode());
				}
			      //角色为代理商或商服宝会员或父级id为代理商            
				 if(searchCondition.getRoleId().equals(Role.AGENT.getCode())||searchCondition.getRoleId().equals(Role.SFB_AGENT.getCode())){
					 sb.append("    and exists(select 1                                                        \n");
		    	        sb.append("           from tbl_mer_agent_relation ma                                      \n");
		    	        sb.append("          where ma.merno = t.mcht_cd                                           \n");
		    	        sb.append("            and ma.agentid =").append(op.getId()).append(" ) \n");
				 }
				  //登录人角色销售人员        
				 if(searchCondition.getRoleId().equals(Role.MARKET_DIRECT.getCode())||searchCondition.getRoleId().equals(Role.MARKET_LEAD.getCode())){                                               
		    	        sb.append("    and exists (select 1                                                      \n");
		    	        sb.append("                   from t_oper_role orole           \n");
		    	        sb.append("                  where t.uc_bc_cd_area = orole.agentid                             \n");
		    	        sb.append("                    and orole.operid =").append(op.getId()).append("  )   \n");
	 			}
				Object[] parmas={list.get(0).toString().trim()};
             List<Object> name_list = pageDao.querySqlAll(sb.toString(), parmas);		
			  if(name_list.isEmpty()){
				  resultData.add(list.get(0));
				  resultData.add("商户号不存在或不属于当前登录人员");
				  count++;
				  res.add(resultData);
				  continue;
			  }	
           	  for (Object obj : name_list) {
           		Object[] ob = (Object[])obj;
           	    resultData.add(ob[0]);
           	    resultData.add(ob[1]);
             	resultData.add(ob[2]);
              	resultData.add(ob[3]);
            	resultData.add(ob[4]);
            	count++;
           	     }
           	 res.add(resultData);
           	
			} catch (Exception e) {
				resultData.add(list.get(0));
				resultData.add("异常情况发生，该条商户查询失败");
			    String s=(String) list.get(0);
			    resultMap.put("result", "异常情况发生，商户号【"+s+"】查询失败");
				res.add(resultData);
				continue;
			}
		}
        	logger.info("商户号导入结束！！");
        	resultMap.put("data",res);
        	resultMap.put("result","导入成功，存在【"+count+"】条数据!!!");
 		   
		return true;
		
	}

	/**
	 * 将File装换成Map集合
	 * @param file
	 * @param map
	 */
	@SuppressWarnings("deprecation")
	protected boolean transfromToExcel(File file,LinkedHashMap<Integer,List<Object>> map,LinkedHashMap<String,Object> resultMap)
	{
		try {
			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			XSSFWorkbook xwb = new XSSFWorkbook(file.getPath());
			// 得到Excel工作表对象
			XSSFSheet sheet = xwb.getSheetAt(0);
			// 得到行数
			int trLength = sheet.getPhysicalNumberOfRows();
			if(trLength == 0){
				logger.info("上传文件缺少列名");
				resultMap.put("result", "上传文件缺少列名");
				return false;
			}			
			logger.info("Excel中记录的总行数是：" + (trLength - 1));			
			// 每条记录列数
			Row row = sheet.getRow(0);
						
			int tdLength = row.getPhysicalNumberOfCells();
			if(tdLength<1){
				logger.info("上传的文件缺少必需的列名");
				resultMap.put("result", "上传的文件缺少必需的列名");
				return false;
			}
			if(tdLength>1){
				logger.info("上传的文件存在多余的列名");
				resultMap.put("result", "上传的文件存在多余的列名");
				return false;
			}
			logger.info("每条记录的列数："+tdLength);
				
			//验证标题是否一致
			String reason_error=null;
			String[] title={"商户号"};
			String titleName=null;
				row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);//CELL_TYPE_STRING
				titleName=row.getCell(0).getStringCellValue();
				if(!titleName.equals(title[0]))
				{
					reason_error="第1行第1列的列名不正确,正确的列名为【"+title[0]+"】";
					logger.info(reason_error);
					resultMap.put("result", reason_error);
					return false;
				}
			
			if(trLength == 1){
				logger.info("上传文件内容为空");
				resultMap.put("result", "上传文件内容为空");
				return false;
			}
			for (int i = 1; i < trLength; i++) {
				List<Object> tdList = new ArrayList<Object>();
				Row r = sheet.getRow(i);
				
				for (int j = 0; j < tdLength; j++) {
					Cell c = r.getCell(j);
					/*
					 * 将所有列的内容都设置成String类型，否则会出Excel异常： Cannot get a text value
					 * from a numeric cell
					 */
					if (c != null) {
						c.setCellType(Cell.CELL_TYPE_STRING);
						tdList.add(c.getStringCellValue());
					}else{
						tdList.add("");
					}					
				}
				map.put(i, tdList);
			}
		} catch (Exception e) {
			logger.error("将Excel中数据封装到List容器中异常：" + e.getMessage());
			resultMap.put("result", "将Excel中数据封装到List容器中异常："+ e.getMessage());
			return false;
		}
		return true;
	}
	
	/**
	 * 验证数据正确性
	 * @param map
	 */
	public boolean verifyDataCorrectness(Map<Integer,List<Object>> map,Map<String,Object> resultMap)
	{
		Iterator<Entry<Integer, List<Object>>> it=map.entrySet().iterator();
		String error_reason=null;
		while(it.hasNext())
		{
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();			
			//商户号
			String regex="\\d{1,15}";
			String num=Constance.Null2EmptyTrim(list.get(0));
			if(!Pattern.matches(regex,num))
			{
				error_reason="第"+(entry.getKey()+1)+"行第1列商户号不规范,只能包含数字字符且长度不能超过15，也不能为空";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
		}
		return true;
	}
}
